package com.uchedao.vehicledetection.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.google.gson.Gson;
import com.uchedao.vehicledetection.R;
import com.uchedao.vehicledetection.global.GlobalInfo;
import com.uchedao.vehicledetection.model.Accident;
import com.uchedao.vehicledetection.model.BaseInfo;
import com.uchedao.vehicledetection.model.Condition;
import com.uchedao.vehicledetection.model.Draft;
import com.uchedao.vehicledetection.model.Photo;
import com.uchedao.vehicledetection.model.ResultBoolean;
import com.uchedao.vehicledetection.model.ResultInt;
import com.uchedao.vehicledetection.model.ResultIntArrayPics;
import com.uchedao.vehicledetection.utils.Util;

import java.util.ArrayList;

public class DbHelper extends SQLiteOpenHelper {
    public static final String KEY_DRAFT_CAR_PLATE = "car_plate";// 车牌

    public static final String KEY_DRAFT_CAR_VIN = "car_vin";// vin码

    public static final String KEY_DRAFT_INFO_NUMBER = "info_number";// 车源id

    public static final String KEY_DRAFT_OWNERS_NAME = "owners_name";// 用户名

    public static final String KEY_DRAFT_CREATE_TIME = "create_time";// 创建时间

    public static final String KEY_DRAFT_UPDATE_TIME = "update_time";// 更新时间

    public static final String KEY_DRAFT_BASE_INFO = "base_info";// 基本信息

    public static final String KEY_DRAFT_BASE_INFO_STATUS = "base_info_status";// 基础信息完成状态

    public static final String KEY_DRAFT_CONDITION = "condition";// 综合检测

    public static final String KEY_DRAFT_CONDITION_STATUS = "condition_status";// 综合检测完成状态

    public static final String KEY_DRAFT_ACCIDENT = "accident";// 事故勘察

    public static final String KEY_DRAFT_ACCIDENT_STATUS = "accident_status";// 事故勘察完成状态

    public static final String KEY_DRAFT_DIP = "dip";// 浸泡检测

    public static final String KEY_DRAFT_DIP_STATUS = "dip_status";// 浸泡检测完成状态

    public static final String KEY_DRAFT_FIRE = "fire";// 失火检测

    public static final String KEY_DRAFT_FIRE_STATUS = "fire_status";// 失火检测完成状态

    public static final String KEY_DRAFT_PHOTO = "photo";// 照片上传

    public static final String KEY_DRAFT_PHOTO_STATUS = "photo_status";// 照片上传完成状态

    private static final String DB_NAME = "cjd";

    private static final String DRAFT_TABLE_NAME = "draft";

    /**
     * 本地DB数据库版本号，数据库有变动时需修改，以便升级
     */
    private static final int VER_INT = 4;

    public static final String KEY_UPLOAD_STATUS = "upload_status";// 失火检测完成状态

    private static final String DRAFT_TABLE_SQL_CREATOR = "CREATE TABLE IF NOT EXISTS [" + DRAFT_TABLE_NAME
            + "] ( [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [" + KEY_DRAFT_CAR_PLATE
            + "] VARCHAR(16) NOT NULL, [" + KEY_DRAFT_CAR_VIN + "] VARCHAR(32), [" + KEY_DRAFT_INFO_NUMBER
            + "] VARCHAR(32), [" + KEY_DRAFT_OWNERS_NAME + "] VARCHAR(32), [" + KEY_DRAFT_CREATE_TIME + "] String, ["
            + KEY_DRAFT_UPDATE_TIME + "] String, [" + KEY_DRAFT_BASE_INFO + "] String, [" + KEY_DRAFT_BASE_INFO_STATUS
            + "] INTEGER DEFAULT 0, [" + KEY_DRAFT_CONDITION + "] String, [" + KEY_DRAFT_CONDITION_STATUS
            + "] INTEGER DEFAULT 0, [" + KEY_DRAFT_ACCIDENT + "] String, [" + KEY_DRAFT_ACCIDENT_STATUS
            + "] INTEGER DEFAULT 0, [" + KEY_DRAFT_PHOTO + "] String, [" + KEY_DRAFT_PHOTO_STATUS
            + "] INTEGER DEFAULT 0, [" + KEY_DRAFT_DIP + "] String, [" + KEY_DRAFT_DIP_STATUS
            + "] INTEGER DEFAULT 0, [" + KEY_DRAFT_FIRE + "] String, [" + KEY_DRAFT_FIRE_STATUS
            + "] INTEGER DEFAULT 0, [" + KEY_UPLOAD_STATUS + "] INTEGER DEFAULT 0); ";

    private static final String PHOTO_TABLE_NAME = "photo";

    public static final String KEY_PHOTO_FILE_PATH = "file_path";// 文件路径

    public static final String KEY_PHOTO_FILE_ID = "file_id";// 文件ID

    public static final String CREATE_PHOTO_UPLOAD_TABLE_SQL = "CREATE TABLE IF NOT EXISTS [" + PHOTO_TABLE_NAME
            + "](id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [" + KEY_PHOTO_FILE_PATH + "] TEXT NOT NULL, ["
            + KEY_PHOTO_FILE_ID + "] TEXT NOT NULL);";

    private SQLiteDatabase db;

    public DbHelper(Context context) {
        super(context, DB_NAME, null, VER_INT);
        db = getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // this.db = db;
        db.execSQL(DRAFT_TABLE_SQL_CREATOR);
        db.execSQL(CREATE_PHOTO_UPLOAD_TABLE_SQL);
    }


    private ContentValues createDrafCarPlateContentValues(String carPlate) {
        ContentValues values = new ContentValues();
        values.put(KEY_DRAFT_CAR_PLATE, carPlate);
        return values;
    }

    private ContentValues createDrafVinContentValues(String vin) {
        ContentValues values = new ContentValues();
        values.put(KEY_DRAFT_CAR_VIN, vin);
        return values;
    }

    private ContentValues createDrafContentValues(String carPlate, String vin, String infoNumber) {
        ContentValues values = new ContentValues();
        values.put(KEY_DRAFT_CAR_PLATE, carPlate);
        values.put(KEY_DRAFT_CAR_VIN, vin);
        values.put(KEY_DRAFT_INFO_NUMBER, infoNumber);
        return values;
    }

    /**
     * 插入一条记录 数据库若存在此车牌记录则更新，不存在插入一条新记录
     *
     * @param carPlate
     * @param vin
     * @param infoNumber
     */
    public void insertDraf(String carPlate, String vin, String infoNumber) {
        if (drafHasRecord(carPlate)) {// 记录已存在
            updateDraf(carPlate, vin, infoNumber);
        } else {
            insertDraf(createDrafContentValues(carPlate, vin, infoNumber));
        }
    }

    /**
     * 插入记录
     *
     * @param values
     */
    public void insertDraf(ContentValues values) {
        try {
            long time = System.currentTimeMillis();
            values.put(KEY_DRAFT_CREATE_TIME, Util.formatDataTime(time));
            values.put(KEY_DRAFT_UPDATE_TIME, Util.formatDataTime(time));
            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.insert(DRAFT_TABLE_NAME, null, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 更新记录
     *
     * @param carPlate
     * @param vin
     * @param infoNumber
     */
    public boolean updateDraf(String carPlate, String vin, String infoNumber) {
        return updateDraf(carPlate, createDrafContentValues(carPlate, vin, infoNumber));
    }

    /**
     * 更新车牌号
     *
     * @param carPlate
     */
    public boolean updateDrafCarPlateByByInfoId(String info_number, String carPlate) {
        return updateDrafByInfoId(info_number, createDrafCarPlateContentValues(carPlate));
    }

    /**
     * 更新vin
     *
     * @param carPlate
     */
    public boolean updateDrafVinByInfoId(String info_number, String carPlate) {
        return updateDrafByInfoId(info_number, createDrafVinContentValues(carPlate));
    }


    /**
     * @param info_number
     * @param values
     * @功能描述 更新
     * @author 叶晋
     * @修改时间 2012-10-11 下午5:37:36
     */
    public boolean updateDrafByInfoId(String info_number, ContentValues values) {
        try {
            values.put(KEY_DRAFT_UPDATE_TIME, Util.formatDataTime(System.currentTimeMillis()));
            if (db == null)
                db = getWritableDatabase();
            synchronized (db) {
                if (db.isOpen()) {
                    db.update(DRAFT_TABLE_NAME, values, KEY_DRAFT_INFO_NUMBER + "=?", new String[]{info_number});
                    return true;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * @param carPlate
     * @param values
     * @功能描述 更新
     * @author 叶晋
     * @修改时间 2012-10-11 下午5:37:36
     */
    public boolean updateDraf(String carPlate, ContentValues values) {
        try {
            values.put(KEY_DRAFT_UPDATE_TIME, Util.formatDataTime(System.currentTimeMillis()));
            if (db == null)
                db = getWritableDatabase();
            synchronized (db) {
                if (db.isOpen()) {
                    db.update(DRAFT_TABLE_NAME, values, KEY_DRAFT_CAR_PLATE + "=?", new String[]{carPlate});
                    return true;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 记录是否存在
     *
     * @param carPlate 车牌号
     * @return
     */
    public boolean drafHasRecord(String carPlate) {
        Cursor c = queryCarPlate(carPlate);
        if (c == null)
            return false;
        boolean flag = c.getCount() > 0;
        c.close();
        c = null;
        return flag;
    }

    /**
     * 得到记录数
     *
     * @return
     */
    public int getDrafCount() {
        if (db == null)
            db = getWritableDatabase();
        if (!db.isOpen())
            return 0;
        Cursor c = db.rawQuery("select * from " + DRAFT_TABLE_NAME, null);

        if (c == null)
            return 0;
        int count = c.getCount();
        c.close();
        c = null;
        return count;
    }

    /**
     * 查询车牌
     *
     * @param carPlate
     * @return
     */
    public Cursor queryCarPlate(String carPlate) {
        if (db == null)
            db = getWritableDatabase();
        if (!db.isOpen())
            return null;
        return db.rawQuery("select * from " + DRAFT_TABLE_NAME + " where " + KEY_DRAFT_CAR_PLATE + "=?",
                new String[]{carPlate});
    }

    /**
     * 得到某车牌的草稿数据
     *
     * @param carPlate
     * @return
     */
    public Draft getDraft(String carPlate) {
        Cursor c = queryCarPlate(carPlate);
        if (c != null) {
            if (c.getCount() > 0) {
                c.moveToFirst();
                return getDraft(c);
            }
            c.close();
            c = null;
        }
        return null;
    }

    /**
     * 查询所有数据
     *
     * @return
     */
    public Cursor queryAllDraf() {
        if (db == null)
            db = getWritableDatabase();
        if (!db.isOpen())
            return null;
//		return db.rawQuery("select * from " + DRAFT_TABLE_NAME, null);
        return db.rawQuery("select * from " + DRAFT_TABLE_NAME + " order by " + KEY_DRAFT_CREATE_TIME + " desc", null);
    }

    /**
     * 得到所有草稿
     *
     * @return
     */
    public ArrayList<Draft> getAllDraft() {
        ArrayList<Draft> list = new ArrayList<Draft>();
        Cursor c = queryAllDraf();
        if (c != null) {
            c.moveToFirst();
            while (!c.isAfterLast()) {
                Draft draft = getDraft(c);
                if (draft != null && draft.upload_status == 0) {
                    list.add(draft);
                }
                c.moveToNext();
            }
            c.close();
            c = null;
        }
        return list;
    }

    /**
     * 得到AppItem
     *
     * @param c
     * @return
     * @author 叶晋
     * @修改时间 2012-10-17 上午11:30:24
     */
    @SuppressWarnings("unchecked")
    private Draft getDraft(Cursor c) {
        Draft draft = new Draft();

        try {
            draft.car_plate = c.getString(1);
            draft.car_vin = c.getString(2);
            draft.info_number = c.getString(3);
            draft.create_time = c.getString(5);
            draft.update_time = c.getString(6);
            draft.base_info = new Gson().fromJson(c.getString(7), BaseInfo.class);
            draft.status[0] = c.getInt(8);
            draft.condition = new Gson().fromJson(c.getString(9), Condition.class);
            draft.status[1] = c.getInt(10);
            draft.accident = new Gson().fromJson(c.getString(11), Accident.class);
            draft.status[2] = c.getInt(12);
            draft.photo = new Gson().fromJson(c.getString(13), Photo[].class);
            draft.status[3] = c.getInt(14);
            draft.dip = new Gson().fromJson(c.getString(15), ResultBoolean[].class);
            draft.status[4] = c.getInt(16);
            draft.fire = new Gson().fromJson(c.getString(17), ResultBoolean[].class);
            draft.status[5] = c.getInt(18);
            draft.upload_status = c.getInt(19);

            if (draft.base_info == null)
                draft.base_info = new BaseInfo();
            if (draft.condition == null)
                draft.condition = new Condition();
            if (draft.accident == null)
                draft.accident = new Accident();
            if (draft.dip == null)
                draft.dip = new ResultBoolean[15];
            if (draft.fire == null)
                draft.fire = new ResultBoolean[8];
            if (draft.photo == null)
                draft.photo = new Photo[31];

            // 初始化漆面检测
            String header = GlobalInfo.resources.getString(R.string.check_part);
            String[] texts = GlobalInfo.resources.getStringArray(R.array.paint_car_entries);
            for (int i = 0; i < texts.length; i++) {
                if (draft.condition.paint_surface[i] == null)
                    draft.condition.paint_surface[i] = new ResultIntArrayPics();
                draft.condition.paint_surface[i].header = header;
                draft.condition.paint_surface[i].name = texts[i];
                if (draft.condition.paint_surface[i].result == null) {
                    draft.condition.paint_surface[i].result = new ArrayList<Integer>();
                }
                if (draft.condition.paint_surface[i].result.size() == 0)
                    draft.condition.paint_surface[i].result.add(0);
            }
            // 初始化内饰检测
            String[] headers = GlobalInfo.resources.getStringArray(R.array.interior_tabspec_entries);
            int[] arrayId = {R.array.interior_front_entries, R.array.interior_back_entries, R.array.interior_other_entries};
            for (int i = 0, k = 0; i < headers.length; i++) {
                texts = GlobalInfo.resources.getStringArray(arrayId[i]);
                for (String text : texts) {
                    if (draft.condition.interior[k] == null)
                        draft.condition.interior[k] = new ResultIntArrayPics();
                    draft.condition.interior[k].name = text;
                    if (draft.condition.interior[k].result == null) {
                        draft.condition.interior[k].result = new ArrayList<Integer>();
                    }
                    if (draft.condition.interior[k].result.size() == 0)
                        draft.condition.interior[k].result.add(0);
                    k++;
                }
            }
            // 初始化发动机变速箱检测
            String[] values = GlobalInfo.resources.getStringArray(R.array.engine_obd_values);
            for (int i = 0; i < values.length; i++) {
                draft.condition.performance.engine_gearbox[i] = new ResultInt();
                draft.condition.performance.engine_gearbox[i].name = values[i];
                draft.condition.performance.engine_gearbox[i].result = -1;
            }
            // 初始化加强件
            header = GlobalInfo.resources.getString(R.string.check_part);
            texts = GlobalInfo.resources.getStringArray(R.array.reinforcement_values);
            for (int i = 0; i < texts.length; i++) {
                if (draft.accident.reinforcement[i] == null) {
                    draft.accident.reinforcement[i] = new ResultInt();
                    draft.accident.reinforcement[i].name = texts[i];
                }
            }
        } catch (Exception e) {
            return null;
        }
        return draft;
    }

    /**
     * 删除记录
     *
     * @param carPlate
     */
    public void deleteDraf(String carPlate) {
        try {
            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.delete(DRAFT_TABLE_NAME, KEY_DRAFT_CAR_PLATE + "=?", new String[]{carPlate});
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询车牌
     *
     * @param carPlate
     * @return
     */
    public Cursor queryPhotoFileId(String carPlate) {

        if (db == null)
            db = getWritableDatabase();
        if (!db.isOpen())
            return null;
        return db.rawQuery("select * from " + PHOTO_TABLE_NAME + " where " + KEY_PHOTO_FILE_PATH + "=?",
                new String[]{carPlate});

    }

    /**
     * 插入一条记录
     */
    public void insertPhoto(String filePath, String fileId) {
        ContentValues values = new ContentValues();
        values.put(KEY_PHOTO_FILE_PATH, filePath);
        values.put(KEY_PHOTO_FILE_ID, fileId);
        try {
            if (db == null)
                db = getWritableDatabase();
            if (db.isOpen())
                db.insert(PHOTO_TABLE_NAME, null, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取图片对应的ID
     *
     * @param filePath
     * @return
     */
    public String getPhotoFileId(String filePath) {
        Cursor c = queryPhotoFileId(filePath);
        if (c != null) {
            if (c.getCount() > 0) {
                c.moveToFirst();
                return c.getString(2);
            }
            c.close();
            c = null;
        }
        return null;
    }

    public void close() {
        if (db != null)
            synchronized (db) {
                if (db.isOpen())
                    db.close();
                db = null;
            }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < newVersion && newVersion == VER_INT) {
            String drop_draft = "drop table " + DRAFT_TABLE_NAME;
            db.execSQL(drop_draft);
            db.execSQL(DRAFT_TABLE_SQL_CREATOR);
        }
    }
}
